package com.rui1024.dao.impl;

import com.rui1024.dao.DiaryDao;
import com.rui1024.pojo.Diary;
import com.rui1024.util.DateUtil;
import com.rui1024.util.PageUtil;
import com.rui1024.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DiaryDaoImpl implements DiaryDao {

    @Override
    public List<Diary> diaryList(Connection con, PageUtil pageUtil,Diary s_diary) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM t_diary t1,t_diaryType t2 WHERE t1.`typeId`=t2.`diaryTypeId`");
        if(s_diary.getTypeId()!=-1){
            sb.append( " and t1.typeId="+s_diary.getTypeId());
        }
        if(StringUtil.isNotEmpty(s_diary.getReleaseDateStr())){
            sb.append( " and DATE_FORMAT(t1.releaseDate,'%Y年%m月')='"+s_diary.getReleaseDateStr()+"'");
        }
        if(StringUtil.isNotEmpty(s_diary.getTitle())){
            sb.append( " and t1.title like '%"+s_diary.getTitle()+"%'");
        }
        sb.append(" ORDER BY t1.`releaseDate` DESC");
        if(pageUtil!=null){
            sb.append(" limit "+pageUtil.getStart()+","+pageUtil.getPageSize()+"");
        }
        System.out.println(sb.toString());
        PreparedStatement pstmt = con.prepareStatement(sb.toString());
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Diary> diaryList = new ArrayList<>();
        while (rs.next()){
            Diary diary = new Diary();
            diary.setDiaryId(rs.getInt("diaryId"));
            diary.setTitle(rs.getString("title"));
            diary.setContent(rs.getString("content"));
            diary.setReleaseDate(DateUtil.formatString(rs.getString("releaseDate"),"yyyy-MM-dd HH:mm:ss"));
            diaryList.add(diary);
        }
        return diaryList;
    }

    @Override
    public int diaryCount(Connection con,Diary s_diary) throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT COUNT(*) AS total FROM t_diary t1,t_diaryType t2 WHERE t1.`typeId`=t2.`diaryTypeId`");
        if(s_diary.getTypeId()!=-1){
            sb.append( " and t1.typeId="+s_diary.getTypeId());
        }
        if(StringUtil.isNotEmpty(s_diary.getReleaseDateStr())){
            sb.append( " and DATE_FORMAT(t1.releaseDate,'%Y年%m月')='"+s_diary.getReleaseDateStr()+"'");
        }
        if(StringUtil.isNotEmpty(s_diary.getTitle())){
            sb.append( " and t1.title like '%"+s_diary.getTitle()+"%'");
        }
        PreparedStatement pstmt = con.prepareStatement(sb.toString());
        ResultSet rs = pstmt.executeQuery();
        if(rs.next()){
            return rs.getInt("total");
        }else{
            return 0;
        }
    }

    @Override
    public List<Diary> diaryCountList(Connection con) throws Exception {
        String sql="SELECT COUNT(t1.diaryId) AS diaryCount, DATE_FORMAT(t1.releaseDate,'%Y年%m月') AS releaseDateStr FROM t_diary t1,t_diaryType t2 WHERE t1.`typeId`=t2.`diaryTypeId` GROUP BY DATE_FORMAT(t1.releaseDate,'%Y年%m月') ORDER BY DATE_FORMAT(t1.releaseDate,'%Y年%m月') DESC";

        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Diary> diaryCountList = new ArrayList<>();
        while (rs.next()){
            Diary diary = new Diary();
            diary.setReleaseDateStr(rs.getString("releaseDateStr"));
            diary.setDiaryCount(rs.getInt("diaryCount"));
            diaryCountList.add(diary);
        }
        return diaryCountList;
    }

    @Override
    public Diary diaryShow(Connection con, int diaryId) throws Exception {
        String sql="SELECT * FROM t_diary t1,t_diaryType t2 WHERE t1.`typeId`=t2.`diaryTypeId` and t1.diaryId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,diaryId);
        ResultSet rs = pstmt.executeQuery();
        Diary diary=null;
        if(rs.next()){
            diary = new Diary();
            diary.setDiaryId(rs.getInt("diaryId"));
            diary.setTitle(rs.getString("title"));
            diary.setContent(rs.getString("content"));
            diary.setTypeId(rs.getInt("typeId"));
            diary.setTypeName(rs.getString("typeName"));
            diary.setReleaseDate(DateUtil.formatString(rs.getString("releaseDate"),"yyyy-MM-dd HH:mm:ss"));
        }
        return diary;
    }

    @Override
    public int diaryAdd(Connection con, Diary diary) throws Exception {
        String sql="insert into t_diary values(null,?,?,?,now())";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,diary.getTitle());
        pstmt.setString(2,diary.getContent());
        pstmt.setInt(3,diary.getTypeId());
        return pstmt.executeUpdate();
    }

    @Override
    public int diaryDelete(Connection con, int diaryId) throws Exception {
        String sql="delete from t_diary where diaryId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,diaryId);
        return pstmt.executeUpdate();
    }

    @Override
    public int diaryUpdate(Connection con, Diary diary) throws Exception {
        String sql="update t_diary set title=?,content=?,typeId=? where diaryId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,diary.getTitle());
        pstmt.setString(2,diary.getContent());
        pstmt.setInt(3,diary.getTypeId());
        pstmt.setInt(4,diary.getDiaryId());
        return pstmt.executeUpdate();
    }

    @Override
    public boolean findByTypeId(Connection con, int typeId) throws Exception {
        String sql="select * from t_diary where typeId=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,typeId);
        ResultSet rs = pstmt.executeQuery();
        if(rs.next()){
            return true;
        }else{
            return false;
        }
    }
}
